package com.wanmait.cookbook.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.cookbook.vo.Cookbook;
import com.wanmait.cookbook.vo.Pager;
import com.wanmait.cookbook.vo.Userinfo;
import com.wanmait.cookbook.vo.Works;
import com.wanmait.cookbook.vo.WorksComment;
import com.wanmait.demo.util.DBHelper;

/*
 * 浣滃搧琛�
 */
public class WorksDAO {
	private WorksDAO() {}
	private static WorksDAO worksDAO = new WorksDAO();
	public static WorksDAO getWorksDAO() {
		return worksDAO;

	}
	/**
     *  上传作品
     * @param works
     */
    public void upWorks(Works works) {
      String sql = "INSERT INTO works(userinfo_id,title,info,cookbook_id,pic) VALUES(?,?,?,?,?)";
      DBHelper.getHelper().executeUpdate(sql, works.getUserinfo().getId(),works.getTitle(),works.getInfo(),works.getCookbook().getId(),works.getPic());
    }
	

	//点击作品图片或是名字查询详细信息
	public List<Works> detailWorks(String name){
		List<Works> burryNames=new ArrayList<Works>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Works works=null;
		Userinfo userinfo=null;
		try {
			String sql=" SELECT * FROM userinfo, works,works_images  "
					+ " WHERE works.title like ? "
					+ " AND  userinfo.id=works.userinfo_id "
					+ " AND works_images.id=works.id AND works.enable=1";
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
		
			rs=ps.executeQuery();
		
			while(rs.next()) {
				works=new Works();
				userinfo=new Userinfo();
				works.setEnable(rs.getBoolean("works.enable"));
				works.setHot(rs.getBoolean("works.hot"));
				works.setId(rs.getInt("id"));
				works.setInfo(rs.getString("works.info"));
				works.setInputTime(rs.getTimestamp("works.input_time"));
				works.setTitle(rs.getString("title"));
				works.setPic(rs.getString("works.pic"));
				userinfo.setAvatar(rs.getString("userinfo.avatar"));
				userinfo.setName(rs.getString("userinfo.name"));
				userinfo.setId(rs.getInt("userinfo_id"));
				works.setUserinfo(userinfo);
				burryNames.add(works);
				
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		
			return burryNames;
		
		
	}
	
	

	public int allWorksRows() {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		try {
			String sql=" SELECT count(*) as allworkRows FROM userinfo, works,works_images "
					+ " WHERE userinfo.id=works.userinfo_id "
					+ " AND works_images.id=works.id AND works.enable=1 AND works.hot=1 ";
			
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			rs.next();
			int allWorksRows=rs.getInt("allworkRows");
			return allWorksRows;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
			
		}
		
		return 0;
	}

	//得到点击页数的作品   热门！

	//寰楀埌鐐瑰嚮椤垫暟鐨勪綔鍝�

	public List<Works> pageAllWorks(Pager pager){
		List<Works>  allWorks=new ArrayList<>();;

		Works works=null;
		Userinfo userinfo=null;
		Connection conn=null;
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 try {
			 //闇�瑕佹煡鍒颁綔鍝�/浣滆�呯瓑淇℃伅
			 //String sql="SELECT *FROM works,works_images WHERE hot=1 AND works.id=works_images.id";
			String sql="SELECT * FROM userinfo, works,works_images "
					+ "WHERE userinfo.id=works.userinfo_id "
					+ "AND works_images.id=works.id AND works.enable=1 AND works.hot=1 "
					+ "limit ?,?";
			 conn=DBHelper.getHelper().getConn();
			 ps=conn.prepareStatement(sql);
			 ps.setInt(1, pager.getStart());
			 ps.setInt(2, pager.getPageSize());
			 rs=ps.executeQuery();
			 while(rs.next()) {
				works=new Works();
				userinfo=new Userinfo();
				works.setEnable(rs.getBoolean("works.enable"));
				works.setHot(rs.getBoolean("works.hot"));
				works.setId(rs.getInt("works.id"));
				works.setInfo(rs.getString("works.info"));
				works.setInputTime(rs.getTimestamp("works.input_time"));
				works.setTitle(rs.getString("title"));
				works.setPic(rs.getString("works.pic"));
				userinfo.setAvatar(rs.getString("userinfo.avatar"));
				userinfo.setName(rs.getString("userinfo.name"));
				userinfo.setId(rs.getInt("userinfo_id"));
				works.setUserinfo(userinfo);
				allWorks.add(works);
				
				
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getHelper().closeConn(rs, ps, conn);
		 }
		 
		
		return allWorks;

	}

	//模糊查询作品的行数
	public int  getBlurryRows(String name) {
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT COUNT(*) AS c   FROM userinfo, works,works_images " );
		sb.append("WHERE works.title like ?");
		sb.append("AND  userinfo.id=works.userinfo_id ");
		sb.append("AND works_images.id=works.id AND works.enable=1");
	
		String sql = sb.toString();
		try 
		{
			conn = DBHelper.getHelper().getConn();
			
			ps = conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
			rs = ps.executeQuery();
			
			rs.next();//移到第一行
			
			int rows = rs.getInt("c");
			
			return rows;
		}
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();//输出异常的信息
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		
		return 0;//出现异常
	}
	// 模糊查询作品
	public List<Works> burryName(String name,Pager pager){
		List<Works> burryNames=new ArrayList<Works>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		Works works=null;
		Userinfo userinfo=null;
		try {
			String sql=" SELECT * FROM userinfo, works,works_images  "
					+ " WHERE works.title like ? "
					+ " AND  userinfo.id=works.userinfo_id "
					+ " AND works_images.id=works.id AND works.enable=1"
					+ " LIMIT ?,? ";
			conn=DBHelper.getHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setString(1, "%"+name+"%");
			ps.setInt(2, pager.getStart());
			ps.setInt(3, pager.getPageSize());
			rs=ps.executeQuery();
		
			while(rs.next()) {
				works=new Works();
				userinfo=new Userinfo();
				works.setEnable(rs.getBoolean("works.enable"));
				works.setHot(rs.getBoolean("works.hot"));
				works.setId(rs.getInt("id"));
				works.setInfo(rs.getString("works.info"));
				works.setInputTime(rs.getTimestamp("works.input_time"));
				works.setTitle(rs.getString("title"));
				works.setPic(rs.getString("works.pic"));
				userinfo.setAvatar(rs.getString("userinfo.avatar"));
				userinfo.setName(rs.getString("userinfo.name"));
				userinfo.setId(rs.getInt("userinfo_id"));
				works.setUserinfo(userinfo);
				burryNames.add(works);
				
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		finally {
			DBHelper.getHelper().closeConn(rs, ps, conn);
		}
		
			return burryNames;
		
		
	}
	
	

	public List<Works> findAllWorks(){
		List<Works>  allWorks=new ArrayList<>();;
		Works works=null;
		Userinfo userinfo=null;
		Cookbook cookbook=null;
		Connection conn=null;
		 PreparedStatement ps=null;
		 ResultSet rs=null;
		 try {
			 //闇�瑕佹煡鍒颁綔鍝�/浣滆�呯瓑淇℃伅
			 //String sql="SELECT *FROM works,works_images WHERE hot=1 AND works.id=works_images.id";
			String sql="SELECT * FROM userinfo, works,works_images WHERE userinfo.id=works.userinfo_id AND works_images.id=works.id AND works.enable=1 AND works.hot=1";
			 conn=DBHelper.getHelper().getConn();
			 ps=conn.prepareStatement(sql);
			 rs=ps.executeQuery();
			 while(rs.next()) {
				works=new Works();
				userinfo=new Userinfo();
				works.setEnable(rs.getBoolean("works.enable"));
				works.setHot(rs.getBoolean("works.hot"));
				works.setId(rs.getInt("id"));
				works.setInfo(rs.getString("works.info"));
				works.setInputTime(rs.getTimestamp("works.input_time"));
				works.setTitle(rs.getString("title"));
				works.setPic(rs.getString("works.pic"));
				userinfo.setAvatar(rs.getString("userinfo.avatar"));
				userinfo.setName(rs.getString("userinfo.name"));
				userinfo.setId(rs.getInt("userinfo_id"));
				works.setUserinfo(userinfo);
				allWorks.add(works);
		
			 }
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		 finally {
			 DBHelper.getHelper().closeConn(rs, ps, conn);
		 }
		 
		
		return allWorks;
	}
	
	//lishuo----------------------------------------
	
	//后台获得所有的作品  分页  全部
		public List<Works> getManageAllWorks(Pager pager){
			List<Works>  allWorks=new ArrayList<>();;
			Works works=null;
			Userinfo userinfo=null;
			Connection conn=null;
			 PreparedStatement ps=null;
			 ResultSet rs=null;
			 try {
				 //需要查到作品/作者等信息
				 //String sql="SELECT *FROM works,works_images WHERE hot=1 AND works.id=works_images.id";
				String sql="SELECT * FROM userinfo, works "
						+ " WHERE userinfo.id=works.userinfo_id "
						+ " AND works.enable=1 "
						+ " limit ?,?";
				 conn=DBHelper.getHelper().getConn();
				 ps=conn.prepareStatement(sql);
				 ps.setInt(1, pager.getStart());
				 ps.setInt(2, pager.getPageSize());
				 rs=ps.executeQuery();
				 while(rs.next()) {
					works=new Works();
					userinfo=new Userinfo();
					works.setEnable(rs.getBoolean("works.enable"));
					works.setHot(rs.getBoolean("works.hot"));
					works.setId(rs.getInt("id"));
					works.setInfo(rs.getString("works.info"));
					works.setInputTime(rs.getTimestamp("works.input_time"));
					works.setTitle(rs.getString("title"));
					works.setPic(rs.getString("works.pic"));
					userinfo.setAvatar(rs.getString("userinfo.avatar"));
					userinfo.setName(rs.getString("userinfo.name"));
					userinfo.setUsername(rs.getString("userinfo.username"));
					userinfo.setId(rs.getInt("userinfo_id"));
					works.setUserinfo(userinfo);
					allWorks.add(works);
				 }
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			 finally {
				 DBHelper.getHelper().closeConn(rs, ps, conn);
			 }
			return allWorks;
		}
	
		
		//后台得到所有的作品数
		public int getManageAllWorksRows() {
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				String sql="SELECT count(*) as allworkRows FROM userinfo, works "
						+ " WHERE userinfo.id=works.userinfo_id "
						+ " AND works.enable=1 ";
				
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				rs=ps.executeQuery();
				rs.next();
				int allWorksRows=rs.getInt("allworkRows");
				return allWorksRows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
			return 0;
		}
		//通过作品id设置作品热门
		public void setWorksHotById(int id,int val) {
			String sql="update works set hot=? where id=?";
			DBHelper.getHelper().executeUpdate(sql,val,id);
		}
		
		//通过ID删除作品
		public void deleteWorksById(int id) {
			String sql="update Works set enable=0 where id=?";
			DBHelper.getHelper().executeUpdate(sql, id);
		}
		
		
		//通过作品IDs删除多个作品
		public void deleteWorksByIds(String ids) {
			String sql="update Works set enable=0 where id in("+ids+")";
			DBHelper.getHelper().executeUpdate(sql);
		}
		
		//通过搜索内容查找作品总数量
		public int findWorksRowsByInput(String val) {
			Connection conn=null;
			PreparedStatement ps=null;
			ResultSet rs=null;
			try {
				String sql = "SELECT count(*) workRows FROM  works,userinfo " + 
						" WHERE  userinfo.id=works.userinfo_id " + 
						" AND (title like ? " + 
						" OR userinfo_id IN " + 
						" (SELECT id FROM userinfo WHERE username like ? ))";
				
				conn=DBHelper.getHelper().getConn();
				ps=conn.prepareStatement(sql);
				ps.setString(1, "%"+val+"%");
				ps.setString(2, "%"+val+"%");
				rs=ps.executeQuery();
				rs.next();
				int allWorksRows=rs.getInt("workRows");
				return allWorksRows;
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}	
			return 0;
		}
		
		
		//通过搜索内容获得作品
		public List<Works> findWorksByInput(Pager pager,String val) {
			List<Works> allWorks = new ArrayList<>();
			Works works = null;
			Userinfo userinfo = null;
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			try {
				String sql = "SELECT * FROM  works,userinfo " + 
						" WHERE  userinfo.id=works.userinfo_id " + 
						" AND (title like ? " + 
						" OR userinfo_id IN " + 
						" (SELECT id FROM userinfo WHERE username like ? ))" 
						+ " limit ?,?";
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				ps.setString(1, "%"+val+"%");
				ps.setString(2, "%"+val+"%");
				ps.setInt(3, pager.getStart());
				ps.setInt(4, pager.getPageSize());
				rs = ps.executeQuery();
				while (rs.next()) {
					works = new Works();
					userinfo = new Userinfo();
					works.setEnable(rs.getBoolean("works.enable"));
					works.setHot(rs.getBoolean("works.hot"));
					works.setId(rs.getInt("id"));
					works.setInfo(rs.getString("works.info"));
					works.setInputTime(rs.getTimestamp("works.input_time"));
					works.setTitle(rs.getString("title"));
					works.setPic(rs.getString("works.pic"));
					userinfo.setAvatar(rs.getString("userinfo.avatar"));
					userinfo.setName(rs.getString("userinfo.name"));
					userinfo.setUsername(rs.getString("userinfo.username"));
					userinfo.setId(rs.getInt("userinfo_id"));
					works.setUserinfo(userinfo);
					allWorks.add(works);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			} finally {
				DBHelper.getHelper().closeConn(rs, ps, conn);
			}
			return allWorks;
		}
		
		// 通过用户ids获得用户发布的作品数量
		public List<Integer> getWorksRowsByUserinfoId(String ids) {
			Connection conn = null;
			PreparedStatement ps = null;
			ResultSet rs = null;
			List<Integer> counts = new ArrayList<Integer>();
			String sql = "SELECT COUNT(*) c FROM works  " 
			+ " WHERE works.userinfo_id IN(" + ids
			+ ") GROUP BY works.userinfo_id ORDER BY userinfo_id";
			try {
				conn = DBHelper.getHelper().getConn();
				ps = conn.prepareStatement(sql);
				rs = ps.executeQuery();
				while (rs.next()) {
					int rows = rs.getInt("c");
					counts.add(rows);
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			return counts;
		}
		
		
		
		//根据作品的id获得作品
				public Works getWorksById(int id) {
					Works works = null;
					Connection conn = null;
					PreparedStatement ps = null;
					ResultSet rs = null;
					String sql="SELECT * FROM works,userinfo "
							+ " WHERE works.id=? "
							+ " AND works.userinfo_id=userinfo.id";
					try {
						conn=DBHelper.getHelper().getConn();
						ps=conn.prepareStatement(sql);
						ps.setInt(1, id);
						rs=ps.executeQuery();
						if(rs.next()) {
							works= new Works();
							works.setId(rs.getInt("works.id"));
							Userinfo userinfo = new Userinfo();
							userinfo.setId(rs.getInt("userinfo.id"));
							userinfo.setUsername(rs.getString("username"));
							works.setUserinfo(userinfo);
							works.setInfo(rs.getString("works.info"));
							works.setPic(rs.getString("pic"));
							works.setTitle(rs.getString("works.title"));
							works.setEnable(rs.getBoolean("works.enable"));
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}finally {
						DBHelper.getHelper().closeConn(rs, ps, conn);
					}
					return works;
				}
				
				
				// 通过ID删除作品
				public void deleteWorksByid(int id) {
					String sql = "update works set enable=0 where id=?";
					DBHelper.getHelper().executeUpdate(sql, id);
				}
				
				// 通过IDs删除多个作品
				public void deleteCookbookCommentsByIds(String ids) {
					String sql = "update works set enable=0 where id in(" + ids + ")";
					DBHelper.getHelper().executeUpdate(sql);
				}
				
				
				//时间段新增作品数量
				public int newWorksYesterdayRows(String y,String d) {
					Connection conn = null;
					PreparedStatement ps =null;
					ResultSet rs = null;
					String sql = "select date(input_time) d, count(1) c from works " + 
							"where input_time >= ? and input_time <= ? " + 
							"group by d order by d DESC ";
					try {
						conn = DBHelper.getHelper().getConn();	
						ps = conn.prepareStatement(sql);
						ps.setString(1, y);
						ps.setString(2, d);
						rs = ps.executeQuery();		
						int sum=0;
						while(rs.next())
						{
							int rows = rs.getInt("c");
							sum +=rows;
						}
						return sum;
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					return 0;
				}
				
				
				// 通过用户ids获得用户发布的作品数量
				public int getWorksCountByUserinfoId(int id) {
					Connection conn = null;
					PreparedStatement ps = null;
					ResultSet rs = null;
	
					String sql = "SELECT COUNT(*) c FROM works WHERE works.userinfo_id=?";
		
					try {
						conn = DBHelper.getHelper().getConn();
						ps = conn.prepareStatement(sql);
						ps.setInt(1, id);
						rs = ps.executeQuery();
						rs.next();
							int rows = rs.getInt("c");
							return rows;
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					}
					return 0;
				}
				
				public List<Works> getWorksByUserinfoId(int id,Pager pager) {
					List<Works> allWorks = new ArrayList<>();
					Works works = null;
					Userinfo userinfo = null;
					Connection conn = null;
					PreparedStatement ps = null;
					ResultSet rs = null;
					try {
						String sql = "SELECT * FROM userinfo, works " 
								+ " WHERE userinfo.id=works.userinfo_id "
								+ " AND works.enable=1 and works.userinfo_id=? limit ?,?";
						conn = DBHelper.getHelper().getConn();
						ps = conn.prepareStatement(sql);
						ps.setInt(1, id);
						ps.setInt(2, pager.getStart());
						ps.setInt(3, pager.getPageSize());
						rs = ps.executeQuery();
						while (rs.next()) {
							works = new Works();
							userinfo = new Userinfo();
							works.setEnable(rs.getBoolean("works.enable"));
							works.setHot(rs.getBoolean("works.hot"));
							works.setId(rs.getInt("works.id"));
							works.setInfo(rs.getString("works.info"));
							works.setInputTime(rs.getTimestamp("works.input_time"));
							works.setTitle(rs.getString("title"));
							works.setPic(rs.getString("works.pic"));
							userinfo.setAvatar(rs.getString("userinfo.avatar"));
							userinfo.setName(rs.getString("userinfo.name"));
							userinfo.setUsername(rs.getString("userinfo.username"));
							userinfo.setId(rs.getInt("userinfo_id"));
							works.setUserinfo(userinfo);
							allWorks.add(works);
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} finally {
						DBHelper.getHelper().closeConn(rs, ps, conn);
					}
					return allWorks;
				}
				
				public List<Works> cetWorksByUserinfoId(int id) {
					List<Works> allWorks = new ArrayList<>();
					Works works = null;
					Userinfo userinfo = null;
					Connection conn = null;
					PreparedStatement ps = null;
					ResultSet rs = null;
					try {
						String sql = "SELECT * FROM userinfo, works " 
								+ " WHERE userinfo.id=works.userinfo_id "
								+ " AND works.enable=1 and works.userinfo_id=?";
						conn = DBHelper.getHelper().getConn();
						ps = conn.prepareStatement(sql);
						ps.setInt(1, id);
						rs = ps.executeQuery();
						while (rs.next()) {
							works = new Works();
							userinfo = new Userinfo();
							works.setEnable(rs.getBoolean("works.enable"));
							works.setHot(rs.getBoolean("works.hot"));
							works.setId(rs.getInt("works.id"));
							works.setInfo(rs.getString("works.info"));
							works.setInputTime(rs.getTimestamp("works.input_time"));
							works.setTitle(rs.getString("title"));
							works.setPic(rs.getString("works.pic"));
							userinfo.setAvatar(rs.getString("userinfo.avatar"));
							userinfo.setName(rs.getString("userinfo.name"));
							userinfo.setUsername(rs.getString("userinfo.username"));
							userinfo.setId(rs.getInt("userinfo_id"));
							works.setUserinfo(userinfo);
							allWorks.add(works);
						}
					} catch (SQLException e) {
						// TODO Auto-generated catch block
						e.printStackTrace();
					} finally {
						DBHelper.getHelper().closeConn(rs, ps, conn);
					}
					return allWorks;
				}
				
				 /**
				   *  展示作品
				   * @param worksId
				   */
				  public Works showWorks(Integer worksId) {
				    Works works = null;
				    Connection conn = null;
				    PreparedStatement ps = null;
				    ResultSet rs = null;
				    String sql = "SELECT * FROM works,userinfo WHERE works.id=? AND works.userinfo_id=userinfo.id";
				    try {
				      conn = DBHelper.getHelper().getConn();
				      ps = conn.prepareStatement(sql);
				      ps.setInt(1, worksId);
				      rs = ps.executeQuery();
				      rs.next();
				      works = new Works();
				      works.setId(rs.getInt("works.id"));
				      Userinfo userinfo = new Userinfo();
				      userinfo.setId(rs.getInt("userinfo.id"));
				      userinfo.setName(rs.getString("userinfo.name"));
				      userinfo.setAvatar(rs.getString("userinfo.avatar"));
				      works.setUserinfo(userinfo);
				      works.setTitle(rs.getString("works.title"));
				      works.setInfo(rs.getString("works.info"));
				      works.setInputTime(rs.getTimestamp("works.input_time"));
				      works.setPic(rs.getString("works.pic"));
				    } catch (SQLException e) {
				      // TODO Auto-generated catch block
				      e.printStackTrace();
				    }
				    finally {
				      DBHelper.getHelper().closeConn(rs, ps, conn);
				    }
				        return works;
				  }
}
